import pandas as pd
import datetime as dt
from datetime import datetime, timedelta
from openpyxl.utils import get_column_letter

start_row_num = 3
blank_row_num = start_row_num - 1

percent_scale = 2


input_filename = r'C:\py-workspace\py-clowler-sty\shenwan\doc\收益凭证业务信息详细表macro.xlsm'
sup_info = pd.read_excel(r'C:\py-workspace\py-clowler-sty\shenwan\doc\龙鼎系列排片.xls')
sup_info = sup_info.set_index('产品代码')
#input_infoname = 'Z:/HQP/0001保本收益凭证/龙鼎规模上限及认购日.xlsx'
interest_date_begin = '2023/10/20'
interest_date_end = '2023/10/20'

def book_name_map():
    return {'000905.SH': '境内指数期权台',
            '000300.SH': '境内指数期权台',
            '000852.SH': '境内指数期权台',
            'AU9999.SGE': '境内商品期权台',
            '000016.SH': '境内指数期权台',
            '159966.SZ': '境内个股期权台',
            'SC2203.INE': '境内商品期权台',
            'AU2206.SHF': '境内商品期权台',
            'SC2202.INE': '境内商品期权台',
            '515030.SH': '境内个股期权台',
            'RB2205.SHF': '境内商品期权台',
            'LH2205.DCE': '境内商品期权台',
            'RB2210.SHF': '境内商品期权台',
            'I2209.DCE': '境内商品期权台',
            'CU.SHF': '境内商品期权台',
            'LH.DCE': '境内商品期权台',
            'SC2204.INE': '境内商品期权台',
            'LH2301.DCE': '	商品期权台',
            'SC00.INE': '境内商品期权台',
            'SWSMART.WI': '大类资产指数',
            '.DCE': '境内商品期权台',
            '.INE': '境内商品期权台',
            '.SHF': '境内商品期权台',
            '.CZC': '境内商品期权台',
            '.CFE': '境内商品期权台',
            '.SGE': '境内商品期权台',
            'HSTECH.HI':'跨境权益期权台',
            'SPTAUUSDOZ.IDC':'跨境商品期权台'
            }


def get_book_name(underlying_id):
    suffix = underlying_id[underlying_id.rindex('.'):]
    if book_name_map().get(suffix) is not None:
        return book_name_map().get(suffix)
    else:
        return book_name_map().get(underlying_id)


def capital_cost_map():
    return {'3M': '3.54%',
            '6M': '3.65%',
            '1Y': '3.73%'}


def get_term(term_days):
    if term_days < 160:
        return '3M'
    elif term_days <= 330:
        return '6M'
    else:
        return '1Y'


def __init_format(df, ws):
    col_list = df.columns.to_list()
    for i in range(len(col_list)):
        ws.column_dimensions[get_column_letter(i + 1)].width = 15


def __format(df, ws, title, formatter):
    col_list = df.columns.to_list()
    if col_list.index(title) < 0:
        return
    col = ws[get_column_letter(col_list.index(title)+1)]
    for cell in col:
        cell.number_format = formatter


def convert(filename, start_date, end_date):
    float_structure_note = '香草及其组合Vanilla'
    # knock_sheet_name = '障碍敲出期权KnockOut'
    # auto_call_sheet_name = '自动赎回AutoCallable'
    # digital_sheet_name = '二元及其组合Digital'

    all_trades = pd.read_excel(filename, sheet_name='收益凭证信息')
    print(all_trades)
    print(sup_info)
    # 查询起息日为指定日期的数据
    interest_date_trades = all_trades[(all_trades['起息日'] >= start_date) & (all_trades['起息日'] <= end_date)]
    output_filename = '收益凭证导出_{}.xlsx'.format(datetime.now().strftime("%Y-%m-%d"))
    writer = pd.ExcelWriter(output_filename, engine='openpyxl')  # , engine='xlsxwriter'

    data_num = 0
    vanilla_trade_df = interest_date_trades.query('结构=="欧式看涨" | 结构=="看涨价差" | 结构=="看跌价差"')
    if len(vanilla_trade_df) > 0:
        vanilla_trades = convert_vanilla(vanilla_trade_df)
        if len(vanilla_trades) > 0:
            data_num += len(vanilla_trades)
            vanilla_df = pd.DataFrame(vanilla_trades)
            vanilla_df.to_excel(writer, sheet_name=vanilla_sheet_name, index=False)
            workbook = writer.book
            vanilla_sheet = workbook[vanilla_sheet_name]
            vanilla_sheet.insert_rows(blank_row_num)
            vanilla_sheet['A'+str(blank_row_num)] = '-'
            __init_format(vanilla_df, vanilla_sheet)
            writer.save()

    knock_trade_df = interest_date_trades.query('结构=="单向看涨鲨鱼鳍" | 结构=="单向看跌鲨鱼鳍" | 结构=="双向鲨鱼鳍" | '
                                                '结构=="欧式单向鲨鱼鳍看涨" | 结构=="欧式单向鲨鱼鳍看跌" | 结构=="欧式双向鲨鱼鳍"')
    if len(knock_trade_df) > 0:
        knock_trades = convert_knock(knock_trade_df)
        if len(knock_trades) > 0:
            data_num += len(knock_trades)
            knock_df = pd.DataFrame(knock_trades)
            knock_df.to_excel(writer, sheet_name=knock_sheet_name, index=False)
            workbook = writer.book
            knock_sheet = workbook[knock_sheet_name]
            knock_sheet.insert_rows(blank_row_num)
            knock_sheet['A'+str(blank_row_num)] = '-'
            __init_format(knock_df, knock_sheet)
            writer.save()

    auto_call_trade_df = interest_date_trades.query('结构=="自动敲出二元看涨"| 结构=="自动敲出二元看跌" | 结构=="自动敲出三元看涨"')
    if len(auto_call_trade_df) > 0:
        auto_call_trades = convert_auto_call(auto_call_trade_df)
        if len(auto_call_trades) > 0:
            data_num += len(auto_call_trades)
            auto_call_df = pd.DataFrame(auto_call_trades)
            auto_call_df.to_excel(writer, sheet_name=auto_call_sheet_name, index=False)
            workbook = writer.book
            auto_call_sheet = workbook[auto_call_sheet_name]
            auto_call_sheet.insert_rows(blank_row_num)
            auto_call_sheet['A'+str(blank_row_num)] = '-'
            __init_format(auto_call_df, auto_call_sheet)
            writer.save()

    digital_trade_df = interest_date_trades.query('结构=="欧式二元看涨"')
    if len(digital_trade_df) > 0:
        digital_trades = convert_digital(digital_trade_df)
        if len(digital_trades) > 0:
            data_num += len(digital_trades)
            digital_df = pd.DataFrame(digital_trades)
            digital_df.to_excel(writer, sheet_name=digital_sheet_name, index=False)
            workbook = writer.book
            digital_sheet = workbook[digital_sheet_name]
            digital_sheet.insert_rows(blank_row_num)
            digital_sheet['A'+str(blank_row_num)] = '-'
            __init_format(digital_df, digital_sheet)
            writer.save()

    if data_num == 0:
        print("本次运行没有写入任何数据.")


def convert_vanilla(trade_df):
    vanilla_trades = []
    trade_index = 0
    for index, row in trade_df.iterrows():
        if pd.isna(row.get('名义本金')) or row.get('名义本金') == 0:
            continue
        vanilla_trade = {}
        vanilla_trade['结构类型'] = ''
        if row.get('结构') in ['欧式看涨']:
            vanilla_trade['结构类型'] = '欧式香草'
        elif row.get('结构') in ['看涨价差', '看跌价差']:
            vanilla_trade['结构类型'] = '欧式价差'

        vanilla_trade['期权类型'] = ''
        if row.get('结构') in ['欧式看涨', '看涨价差']:
            vanilla_trade['期权类型'] = '看涨'
        elif row.get('结构') in ['看跌价差']:
            vanilla_trade['期权类型'] = '看跌'

        vanilla_trade['买卖方向'] = '卖'
        vanilla_trade['标的物代码'] = row.get('标的')
        vanilla_trade['交易编号'] = row.get('代码')
        vanilla_trade['交易确认书编号'] = row.get('代码')
        vanilla_trade['交易日历'] = ''
        vanilla_trade['交易簿'] = get_book_name(row.get('标的'))
        vanilla_trade['起始日'] = row.get('起息日').strftime('%Y/%m/%d')
        vanilla_trade['期末观察日'] = row.get('到期日').strftime('%Y/%m/%d')
        vanilla_trade['计算天数'] = row.get('期限')
        vanilla_trade['交易对手'] = '财富收益凭证'
        vanilla_trade['跨境类型'] = ''
        vanilla_trade['名义本金额'] = row.get('名义本金')
        vanilla_trade['是否年化'] = '是'
        vanilla_trade['年化天数'] = '365'
        #  vanilla_trade['期初价格'] = '=ROUND(I_DQ_Close(D'+str(trade_index+start_row_num)+' , I'+str(trade_index+start_row_num)+') ,2)'
        vanilla_trade['期初价格类型'] = '收盘价格'
        vanilla_trade['期初价格'] = ''
        vanilla_trade['期初汇率'] = ''
        vanilla_trade['行权价格系数'] = ''
        vanilla_trade['行权价格系数2'] = ''
        vanilla_trade['行权价格系数3'] = ''
        vanilla_trade['行权价格系数4'] = ''
        if row.get('结构') in ['欧式看涨']:
            vanilla_trade['行权价格系数'] = round(row.get('行权价') * 100, percent_scale)
        elif row.get('结构') in ['看涨价差', '看跌价差']:
            vanilla_trade['行权价格系数'] = round(row.get('低行权价') * 100, percent_scale)
            vanilla_trade['行权价格系数2'] = round(row.get('行权价') * 100, percent_scale)

        vanilla_trade['参与率'] = round(row.get('参与率') * 100, percent_scale)
        vanilla_trade['参与率2'] = ''
        vanilla_trade['参与率3'] = ''
        vanilla_trade['参与率4'] = ''
        vanilla_trade['收益率'] = ''
        vanilla_trade['最低收益率'] = row.get('固定收益')
        vanilla_trade['是否涉及履约保障'] = '否'
        vanilla_trade['业务类型'] = '收益凭证'
        vanilla_trade['前端期权费率'] = ''
        vanilla_trade['后端期权费率'] = ''
        vanilla_trade['初始预付金率'] = '100%'
        vanilla_trade['追加预付金率'] = ''
        vanilla_trade['返息率'] = ''
        vanilla_trade['前端收益率'] = ''
        vanilla_trade['后端收益率'] = ''
        vanilla_trade['预付金收益率'] = '3.69'
        vanilla_trade['销售费率'] = ''
        vanilla_trade['合约价值折算率'] = ''
        vanilla_trade['初始保障比例参数'] = ''
        vanilla_trade['是否场外完全抵消'] = ''
        vanilla_trade['股份性质'] = ''
        vanilla_trade['MTC结构名称'] = ''
        vanilla_trade['内部资金成本(收益凭证)(自定义)'] = capital_cost_map().get(get_term(row.get('期限')))
        vanilla_trade['是否适用"连续涨跌停提前终止"'] = ''
        vanilla_trade['参考汇率'] = ''
        vanilla_trade['初始保障比例'] = ''
        vanilla_trade['资金账户'] = ''
        vanilla_trade['交易达成日'] = ''
        vanilla_trade['结算日'] = ''
        vanilla_trade['结算模板'] = ''
        vanilla_trade['计算天数类型'] = ''
        vanilla_trade['计算天数截止日类型'] = ''
        vanilla_trade['期末价格类型'] = ''
        vanilla_trade['净额结算'] = ''
        vanilla_trade['我方角色'] = ''
        vanilla_trade['价格保留位数'] = ''
        vanilla_trade['金额保留位数'] = ''
        vanilla_trade['百分比保留位数'] = ''
        vanilla_trade['汇率保留位数'] = ''
        vanilla_trade['期权费类型'] = ''
        vanilla_trade['前端期权费支付日'] = ''
        vanilla_trade['后端期权费支付日'] = ''
        vanilla_trade['保证金计算模式'] = ''
        vanilla_trade['初始预付金支付日'] = ''
        vanilla_trade['追加预付金支付日'] = ''
        vanilla_trade['返息计息本金类型'] = ''
        vanilla_trade['返息年化'] = ''
        vanilla_trade['返息支付日'] = ''
        vanilla_trade['前后端收益计息本金类型'] = ''
        vanilla_trade['前端收益年化'] = ''
        vanilla_trade['前端收益支付日'] = ''
        vanilla_trade['后端收益年化'] = ''
        vanilla_trade['后端收益支付日'] = ''
        vanilla_trade['展期费率'] = ''
        vanilla_trade['行权价格'] = ''
        vanilla_trade['行权价格2'] = ''
        vanilla_trade['行权价格3'] = ''
        vanilla_trade['行权价格4'] = ''
        vanilla_trade['前端期权费'] = ''
        vanilla_trade['后端期权费'] = ''
        vanilla_trade['计算机构'] = ''
        vanilla_trade['对冲方'] = ''
        vanilla_trade['迁移前合约'] = ''
        vanilla_trade['交易确认书编号规则'] = ''
        vanilla_trade['产品名称(交易自定义)']='{}{}{}'.format('申万宏源证券有限公司',row.get('名称'),'收益凭证产品')
        vanilla_trade['本期发行规模上限(交易自定义)'] = sup_info.loc[row.get('代码'),'产品销售总规模上限（亿元）']*100000000
        vanilla_trade['认购期(交易自定义)'] = sup_info.loc[row.get('代码'),'产品参与/开放开始日'].strftime('%Y-%m-%d')
        vanilla_trades.append(vanilla_trade)
        trade_index += 1
    return vanilla_trades


def convert_knock(trade_df):
    knock_trades = []
    trade_index = 0
    for index, row in trade_df.iterrows():
        if pd.isna(row.get('名义本金')) or row.get('名义本金') == 0:
            continue
        knock_trade = {}
        knock_trade['结构类型'] = ''
        knock_trade['期权类型'] = ''
        if row.get('结构') in ['单向看涨鲨鱼鳍', '单向看跌鲨鱼鳍', '欧式单向鲨鱼鳍看涨', '欧式单向鲨鱼鳍看跌']:
            knock_trade['结构类型'] = '单鲨'
        elif row.get('结构') in ['双向鲨鱼鳍', '欧式双向鲨鱼鳍']:
            knock_trade['结构类型'] = '双鲨'

        if row.get('结构') in ['单向看涨鲨鱼鳍', '欧式单向鲨鱼鳍看涨']:
            knock_trade['期权类型'] = '看涨'
        elif row.get('结构') in ['单向看跌鲨鱼鳍', '欧式单向鲨鱼鳍看跌']:
            knock_trade['期权类型'] = '看跌'

        knock_trade['买卖方向'] = '卖'
        knock_trade['标的物代码'] = row.get('标的')
        knock_trade['交易编号'] = row.get('代码')
        knock_trade['交易确认书编号'] = row.get('代码')
        knock_trade['交易簿'] = get_book_name(row.get('标的'))
        knock_trade['交易日历'] = ''
        knock_trade['起始日'] = row.get('起息日').strftime('%Y/%m/%d')
        knock_trade['期末观察日'] = row.get('到期日').strftime('%Y/%m/%d')
        knock_trade['计算天数'] = row.get('期限')
        knock_trade['交易对手'] = '财富收益凭证'
        knock_trade['跨境类型'] = ''
        knock_trade['名义本金额'] = row.get('名义本金')
        knock_trade['是否年化'] = '是'
        knock_trade['年化天数'] = '365'
        # knock_trade['期初价格'] = '=ROUND(I_DQ_Close(D' + str(trade_index + start_row_num) + ' , I' + str(trade_index + start_row_num) + ') ,2)'
        knock_trade['期初价格类型'] = '收盘价格'
        knock_trade['期初价格'] = ''
        knock_trade['期初汇率'] = ''

        knock_trade['行权价格系数'] = ''
        knock_trade['行权价格系数2'] = ''
        if row.get('结构') in ['单向看涨鲨鱼鳍', '单向看跌鲨鱼鳍', '欧式单向鲨鱼鳍看涨', '欧式单向鲨鱼鳍看跌']:
            knock_trade['行权价格系数'] = round(row.get('行权价') * 100, percent_scale)
        elif row.get('结构') in ['双向鲨鱼鳍', '欧式双向鲨鱼鳍']:
            knock_trade['行权价格系数'] = round(row.get('低行权价') * 100, percent_scale)
            knock_trade['行权价格系数2'] = round(row.get('行权价') * 100, percent_scale)

        knock_trade['参与率'] = round(row.get('参与率') * 100, percent_scale)
        knock_trade['参与率2'] = ''
        if row.get('结构') in ['双向鲨鱼鳍', '欧式双向鲨鱼鳍']:
            knock_trade['参与率2'] = round(row.get('参与率') * 100, percent_scale)

        knock_trade['敲出观察类型'] = ''
        if row.get('结构') in ['单向看涨鲨鱼鳍', '单向看跌鲨鱼鳍', '双向鲨鱼鳍']:
            knock_trade['敲出观察类型'] = '每日观察'
        elif row.get('结构') in ['欧式单向鲨鱼鳍看涨', '欧式单向鲨鱼鳍看跌', '欧式双向鲨鱼鳍']:
            knock_trade['敲出观察类型'] = '到期观察'

        knock_trade['敲出价格系数'] = ''
        if row.get('结构') in ['单向看涨鲨鱼鳍', '单向看跌鲨鱼鳍', '欧式单向鲨鱼鳍看涨', '欧式单向鲨鱼鳍看跌']:
            knock_trade['敲出价格系数'] = round(row.get('障碍价') * 100, percent_scale)
        elif row.get('结构') in ['双向鲨鱼鳍', '欧式双向鲨鱼鳍']:
            knock_trade['敲出价格系数'] = round(row.get('低障碍价') * 100, percent_scale)

        knock_trade['敲出收益率'] = row.get('高收益')

        knock_trade['敲出价格系数2'] = ''
        if row.get('结构') in ['双向鲨鱼鳍', '欧式双向鲨鱼鳍']:
            knock_trade['敲出价格系数2'] = round(row.get('障碍价') * 100, percent_scale)

        knock_trade['敲出收益率2'] = ''
        if row.get('结构') in ['双向鲨鱼鳍', '欧式双向鲨鱼鳍']:
            knock_trade['敲出收益率2'] = row.get('高收益')

        knock_trade['收益率'] = ''
        knock_trade['最低收益率'] = row.get('固定收益')
        knock_trade['是否涉及履约保障'] = '否'
        knock_trade['业务类型'] = '收益凭证'
        knock_trade['前端期权费率'] = ''
        knock_trade['后端期权费率'] = ''
        knock_trade['初始预付金率'] = '100%'
        knock_trade['追加预付金率'] = ''
        knock_trade['返息率'] = ''
        knock_trade['前端收益率'] = ''
        knock_trade['后端收益率'] = ''
        knock_trade['预付金收益率'] = '3.69'
        knock_trade['销售费率'] = ''
        knock_trade['合约价值折算率'] = ''
        knock_trade['初始保障比例参数'] = ''
        knock_trade['是否场外完全抵消'] = ''
        knock_trade['股份性质'] = ''
        knock_trade['MTC结构名称'] = ''
        knock_trade['内部资金成本(收益凭证)(自定义)'] = capital_cost_map().get(get_term(row.get('期限')))
        knock_trade['敲出价格类型'] = '收盘价格'
        knock_trade['是否适用"连续涨跌停提前终止"'] = ''
        knock_trade['参考汇率'] = ''
        knock_trade['敲出支付类型'] = '到期支付'
        knock_trade['结算模板'] = ''
        knock_trade['资金账户'] = ''
        knock_trade['行权价格'] = ''
        knock_trade['行权价格2'] = ''
        knock_trade['敲出价格'] = ''
        knock_trade['敲出价格2'] = ''
        knock_trade['前端期权费'] = ''
        knock_trade['后端期权费'] = ''
        knock_trade['开始日期'] = ''
        knock_trade['观察频率'] = ''
        knock_trade['对齐规则'] = ''
        knock_trade['节假日调整'] = ''
        knock_trade['交易达成日'] = ''
        knock_trade['结算日'] = ''
        knock_trade['计算天数类型'] = ''
        knock_trade['计算天数截止日类型'] = ''
        knock_trade['期末价格类型'] = ''
        knock_trade['敲出结算天数'] = ''
        knock_trade['净额结算'] = ''
        knock_trade['我方角色'] = ''
        knock_trade['价格保留位数'] = ''
        knock_trade['金额保留位数'] = ''
        knock_trade['百分比保留位数'] = ''
        knock_trade['汇率保留位数'] = ''
        knock_trade['期权费类型'] = ''
        knock_trade['前端期权费支付日'] = ''
        knock_trade['后端期权费支付日'] = ''
        knock_trade['保证金计算模式'] = ''
        knock_trade['初始预付金支付日'] = ''
        knock_trade['追加预付金支付日'] = ''
        knock_trade['返息计息本金类型'] = ''
        knock_trade['返息年化'] = ''
        knock_trade['返息支付日'] = ''
        knock_trade['前后端收益计息本金类型'] = ''
        knock_trade['前端收益年化'] = ''
        knock_trade['前端收益支付日'] = ''
        knock_trade['后端收益年化'] = ''
        knock_trade['后端收益支付日'] = ''
        knock_trade['展期费率'] = ''
        knock_trade['敲出观察日'] = ''
        knock_trade['敲出结算日'] = ''
        knock_trade['初始保障比例'] = ''
        knock_trade['计算机构'] = ''
        knock_trade['对冲方'] = ''
        knock_trade['迁移前合约'] = ''
        knock_trade['交易确认书编号规则'] = ''
        knock_trade['产品名称(交易自定义)']='{}{}{}'.format('申万宏源证券有限公司',row.get('名称'),'收益凭证产品')
        knock_trade['本期发行规模上限(交易自定义)'] = sup_info.loc[row.get('代码'),'产品销售总规模上限（亿元）']*100000000
        knock_trade['认购期(交易自定义)'] = sup_info.loc[row.get('代码'),'产品参与/开放开始日'].strftime('%Y-%m-%d')
        knock_trades.append(knock_trade)
        trade_index += 1
    return knock_trades


def convert_auto_call(trades_df):
    auto_call_trades = []
    trade_index = 0
    for index, row in trades_df.iterrows():
        if pd.isna(row.get('名义本金')) or row.get('名义本金') == 0:
            continue
        auto_call_trade = {}

        observation_times = row.get('观察次数')

        auto_call_trade['结构类型'] = ''
        if row.get('结构') == '自动敲出二元看涨':
            auto_call_trade['结构类型'] = '保本雪球看涨'
        elif row.get('结构') == '自动敲出二元看跌':
            auto_call_trade['结构类型'] = '保本雪球看跌'
        elif row.get('结构') == '自动敲出三元看涨':
            auto_call_trade['结构类型'] = '三元雪球'

        auto_call_trade['买卖方向'] = '卖'
        auto_call_trade['标的物代码'] = row.get('标的')
        auto_call_trade['交易编号'] = row.get('代码')
        auto_call_trade['交易确认书编号'] = row.get('代码')
        auto_call_trade['交易簿'] = get_book_name(row.get('标的'))
        auto_call_trade['交易日历'] = ''
        auto_call_trade['起始日'] = row.get('起息日').strftime('%Y/%m/%d')
        auto_call_trade['期末观察日'] = row.get('到期日').strftime('%Y/%m/%d')
        auto_call_trade['计算天数'] = row.get('期限')
        auto_call_trade['交易对手'] = '财富收益凭证'
        auto_call_trade['跨境类型'] = ''
        auto_call_trade['名义本金额'] = row.get('名义本金')
        auto_call_trade['是否年化'] = '是'
        if row.get('结构') == '自动敲出三元看涨':
            auto_call_trade['是否年化'] = '否'

        auto_call_trade['年化天数'] = '365'
        # auto_call_trade['期初价格'] = '=ROUND(I_DQ_Close(C' + str(trade_index + start_row_num) + ' , H' + str(trade_index + start_row_num) + '), 2)'
        auto_call_trade['期初价格类型'] = '收盘价格'
        auto_call_trade['期初价格'] = ''
        auto_call_trade['期初汇率'] = ''
        auto_call_trade['行权价格系数'] = ''
        auto_call_trade['行权价格'] = ''
        auto_call_trade['参与率'] = round(row.get('参与率') * 100, percent_scale)
        auto_call_trade['增强参与率'] = ''
        auto_call_trade['敲出价格类型'] = '收盘价格'

        observation_dates = []
        knock_out_prices = []
        knock_out_settle_dates = []
        for i in range(observation_times):
            observation_date = row.get('观察日' + str(i + 1))
            observation_dates.append(row.get('观察日'+str(i+1)).strftime('%Y/%m/%d'))
            knock_out_prices.append(str(round(row.get('障碍价') * 100, percent_scale)))
            knock_out_settle_dates.append(__date_adjust(observation_date).strftime('%Y/%m/%d'))

        observation_dates.append(row.get('到期日').strftime('%Y/%m/%d'))
        knock_out_prices.append(str(round(row.get('障碍价') * 100, percent_scale)))
        knock_out_settle_dates.append(__date_adjust(row.get('到期日')).strftime('%Y/%m/%d'))

        auto_call_trade['敲出价格系数'] = ','.join(knock_out_prices)
        auto_call_trade['敲出价格逐级调整系数'] = '0.0000%'
        auto_call_trade['敲出收益率'] = ''
        if row.get('结构') == '自动敲出二元看涨':
            auto_call_trade['敲出收益率'] = row.get('高收益')-row.get('低收益')
        elif row.get('结构') == '自动敲出二元看跌':
            auto_call_trade['敲出收益率'] = row.get('高收益') - row.get('低收益')
        elif row.get('结构') == '自动敲出三元看涨':
            auto_call_trade['敲出收益率'] = row.get('高收益')

        auto_call_trade['敲出支付类型'] = '立即支付'
        auto_call_trade['敲出观察日'] = ','.join(observation_dates)

        auto_call_trade['敲入价格类型'] = ''
        auto_call_trade['敲入价格系数'] = ''
        if row.get('结构') == '自动敲出三元看涨':
            auto_call_trade['敲入价格类型'] = '收盘价格'
            auto_call_trade['敲入价格系数'] = round(row.get('低障碍价') * 100, percent_scale)

        auto_call_trade['红利票息'] = ''
        if row.get('结构') == '自动敲出二元看涨':
            auto_call_trade['红利票息'] = 0
        elif row.get('结构') == '自动敲出二元看跌':
            auto_call_trade['红利票息'] = 0
        elif row.get('结构') == '自动敲出三元看涨':
            auto_call_trade['红利票息'] = row.get('低收益')

        auto_call_trade['最低收益率'] = ''
        if row.get('结构') == '自动敲出二元看涨':
            auto_call_trade['最低收益率'] = row.get('低收益')
        elif row.get('结构') == '自动敲出二元看跌':
            auto_call_trade['最低收益率'] = row.get('低收益')
        elif row.get('结构') == '自动敲出三元看涨':
            auto_call_trade['最低收益率'] = row.get('固定收益')

        auto_call_trade['是否涉及履约保障'] = '否'
        auto_call_trade['业务类型'] = '收益凭证'
        auto_call_trade['初始预付金率'] = '100%'
        auto_call_trade['内部资金成本(收益凭证)(自定义)'] = capital_cost_map().get('3M')
        auto_call_trade['敲出价格'] = ''
        auto_call_trade['开始日期'] = ''
        auto_call_trade['观察频率'] = ''
        auto_call_trade['对齐规则'] = ''
        auto_call_trade['节假日调整'] = ''
        auto_call_trade['敲出结算天数'] = ''
        # auto_call_trade['敲出结算日'] = ','.join(knock_out_settle_dates)
        auto_call_trade['敲出结算日'] = ''
        auto_call_trade['敲出计算天数'] = ''
        auto_call_trade['敲入价格'] = ''
        auto_call_trade['敲入最低基准收益率'] = ''
        auto_call_trade['价格保留位数'] = ''
        auto_call_trade['金额保留位数'] = ''
        auto_call_trade['百分比保留位数'] = ''
        auto_call_trade['汇率保留位数'] = ''
        auto_call_trade['净额结算'] = ''
        auto_call_trade['我方角色'] = ''
        auto_call_trade['期权费类型'] = ''
        auto_call_trade['前端期权费率'] = ''
        auto_call_trade['前端期权费'] = ''
        auto_call_trade['前端期权费支付日'] = ''
        auto_call_trade['后端期权费率'] = ''
        auto_call_trade['后端期权费'] = ''
        auto_call_trade['后端期权费支付日'] = ''
        auto_call_trade['展期费率'] = ''
        auto_call_trade['初始保障比例'] = ''
        auto_call_trade['保证金计算模式'] = ''
        auto_call_trade['初始预付金支付日'] = ''
        auto_call_trade['追加预付金率'] = ''
        auto_call_trade['追加预付金支付日'] = ''
        auto_call_trade['预付金返息年化'] = ''
        auto_call_trade['预付金返息率'] = ''
        auto_call_trade['预付金返息支付日'] = ''
        auto_call_trade['前后端收益计息本金类型'] = ''
        auto_call_trade['前端收益年化'] = ''
        auto_call_trade['前端收益率'] = ''
        auto_call_trade['前端收益支付日'] = ''
        auto_call_trade['后端收益年化'] = ''
        auto_call_trade['后端收益率'] = ''
        auto_call_trade['后端收益支付日'] = ''
        auto_call_trade['销售费率'] = ''
        auto_call_trade['计算机构'] = ''
        auto_call_trade['对冲方'] = ''
        auto_call_trade['是否适用连续涨跌停提前终止'] = ''
        auto_call_trade['预付金收益率'] = '3.69'
        auto_call_trade['销售(自定义)'] = ''
        auto_call_trade['是否场外完全抵消(自定义)'] = ''
        auto_call_trade['迁移前合约(自定义)'] = ''
        auto_call_trade['交易确认书编号规则'] = ''
        auto_call_trade['MTC结构名称(自定义)'] = ''
        auto_call_trade['股份性质(自定义)'] = ''
        auto_call_trade['合约价值折算率(自定义)'] = ''
        auto_call_trade['初始保障⽐例参数(自定义)'] = ''
        auto_call_trade['产品名称(交易自定义)']='{}{}{}'.format('申万宏源证券有限公司',row.get('名称'),'收益凭证产品')
        auto_call_trade['本期发行规模上限(交易自定义)'] = sup_info.loc[row.get('代码'),'产品销售总规模上限（亿元）']*100000000
        auto_call_trade['认购期(交易自定义)'] = sup_info.loc[row.get('代码'),'产品参与/开放开始日'].strftime('%Y-%m-%d')
        auto_call_trades.append(auto_call_trade)
        trade_index += 1
    return auto_call_trades


def convert_digital(trade_df):
    digital_trades = []
    i = 0
    for index, row in trade_df.iterrows():
        if pd.isna(row.get('名义本金')) or row.get('名义本金') == 0:
            continue
        digital_trade = {}

        digital_trade['结构类型'] = '欧式二元'
        digital_trade['期权类型'] = '看涨'
        digital_trade['买卖方向'] = '卖'
        digital_trade['标的物代码'] = row.get('标的')
        digital_trade['交易编号'] = row.get('代码')
        digital_trade['交易确认书编号'] = row.get('代码')
        digital_trade['交易簿'] = get_book_name(row.get('标的'))
        digital_trade['起始日'] = row.get('起息日').strftime('%Y/%m/%d')
        digital_trade['期末观察日'] = row.get('到期日').strftime('%Y/%m/%d')
        digital_trade['交易对手'] = '财富收益凭证'
        digital_trade['跨境类型'] = ''
        digital_trade['名义本金额'] = row.get('名义本金')
        digital_trade['交易日历'] = ''
        digital_trade['计算天数'] = row.get('期限')
        digital_trade['是否年化'] = '是'
        digital_trade['年化天数'] = '365'
        # digital_trade['期初价格'] = '=ROUND(I_DQ_Close(D' + str(i + start_row_num) + ' , H' + str(
        #     i + start_row_num) + '), 2)'
        digital_trade['期初价格类型'] = '收盘价格'
        digital_trade['期初价格'] = ''
        digital_trade['期初汇率'] = ''
        digital_trade['行权价格系数'] = round(row.get('行权价') * 100, percent_scale)
        digital_trade['行权价格系数2'] = ''
        digital_trade['行权价格系数3'] = ''
        digital_trade['参与率'] = round(row.get('参与率') * 100, percent_scale)
        digital_trade['参与率2'] = ''
        digital_trade['敲出价格类型'] = ''
        digital_trade['敲出支付类型'] = ''
        digital_trade['收益率'] = row.get('高收益') - row.get('低收益')
        digital_trade['收益率2'] = ''
        digital_trade['收益率3'] = ''
        digital_trade['最低收益率'] = row.get('低收益')
        digital_trade['是否涉及履约保障'] = '否'
        digital_trade['业务类型'] = '收益凭证'
        digital_trade['前端期权费率'] = ''
        digital_trade['后端期权费率'] = ''
        digital_trade['初始预付金率'] = '100%'
        digital_trade['追加预付金率'] = ''
        digital_trade['返息率'] = ''
        digital_trade['前端收益率'] = ''
        digital_trade['后端收益率'] = ''
        digital_trade['销售费率'] = ''
        digital_trade['预付金收益率'] = '3.69'
        digital_trade['客户部门(自定义)'] = ''
        digital_trade['是否场外完全抵消(自定义)'] = ''
        digital_trade['内部资金成本(收益凭证)(自定义)'] = capital_cost_map().get(get_term(row.get('期限')))
        digital_trade['股份性质(自定义)'] = ''
        digital_trade['维持保障比例(自定义)'] = ''
        digital_trade['交易确认书编号规则'] = ''
        digital_trade['交易达成日'] = ''
        digital_trade['结算日'] = ''
        digital_trade['结算模板'] = ''
        digital_trade['资金账户'] = ''
        digital_trade['名义本金额类型'] = ''
        digital_trade['计算天数类型'] = ''
        digital_trade['计算天数截止日类型'] = ''
        digital_trade['期末价格类型'] = ''
        digital_trade['参考汇率'] = ''
        digital_trade['行权价格'] = ''
        digital_trade['行权价格2'] = ''
        digital_trade['行权价格3'] = ''
        digital_trade['观察价格类型'] = ''
        digital_trade['净额结算'] = ''
        digital_trade['我方角色'] = ''
        digital_trade['价格保留位数'] = ''
        digital_trade['金额保留位数'] = ''
        digital_trade['百分比保留位数'] = ''
        digital_trade['汇率保留位数'] = ''
        digital_trade['期权费类型'] = ''
        digital_trade['前端期权费'] = ''
        digital_trade['前端期权费支付日'] = ''
        digital_trade['后端期权费'] = ''
        digital_trade['后端期权费支付日'] = ''
        digital_trade['展期费率'] = ''
        digital_trade['初始保障比例'] = ''
        digital_trade['保证金计算模式'] = ''
        digital_trade['初始预付金支付日'] = ''
        digital_trade['追加预付金支付日'] = ''
        digital_trade['返息计息本金类型'] = ''
        digital_trade['返息年化'] = ''
        digital_trade['返息支付日'] = ''
        digital_trade['前后端收益计息本金类型'] = ''
        digital_trade['前端收益年化'] = ''
        digital_trade['前端收益支付日'] = ''
        digital_trade['后端收益年化'] = ''
        digital_trade['后端收益支付日'] = ''
        digital_trade['计算机构'] = ''
        digital_trade['对冲方'] = ''
        digital_trade['是否适用连续涨跌停提前终止'] = ''
        digital_trade['高收益率(自定义)'] = ''
        digital_trade['销售(自定义)'] = ''
        digital_trade['合约价值折算率(自定义)'] = ''
        digital_trade['初始保障⽐例参数(自定义)'] = ''
        digital_trade['迁移前合约(自定义)'] = ''
        digital_trade['MTC结构名称(自定义)'] = ''
        digital_trade['产品名称(交易自定义)']='{}{}{}'.format('申万宏源证券有限公司',row.get('名称'),'收益凭证产品')
        digital_trade['本期发行规模上限(交易自定义)'] = sup_info.loc[row.get('代码'),'产品销售总规模上限（亿元）']*100000000
        digital_trade['认购期(交易自定义)'] = sup_info.loc[row.get('代码'),'产品参与/开放开始日'].strftime('%Y-%m-%d')
        digital_trades.append(digital_trade)
        i += 1
    return digital_trades


def __date_adjust(date):
    if date.weekday() == 3 or date.weekday() == 4:
        return date + timedelta(days=4)
    elif date.weekday() == 5:
        return date + timedelta(days=3)
    else:
        return date + timedelta(days=2)


if __name__ == '__main__':
    # 文件路径, 起息日
    convert(input_filename, interest_date_begin, interest_date_end)
